import dmPython

r"""
默认开启自动提交(autoCommit)模式
"""

class DmDb:

    def __init__(self,
                 user='cigproxy',
                 password='Htzhcig_5873',
                 server='172.17.92.172',
                 port=5236):
        """
        初始化方法,创建连接对象,游标对象
        :param user: 账号(对应模式名)
        :param password:密码
        :param server: 数据库ip
        :param port: 端口
        """
        try:
            self.conn = dmPython.connect(user=user, password=password, server=server, port=port)
            self.cursor = self.conn.cursor()
            if self.cursor:
                print('>>>数据库连接成功<<<')
        except Exception as err:
            print('>>>数据库连接失败：',err)

    def init_cursor(self):
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 对象被销毁的时候执行 关闭连接对象
        self.conn.close()

    def select_data(self, sql):
        """
        查询数据
        :param sql:select语句
        :return: 查询结果
        """
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception as err:
            print(err)

    def execute_one(self, sql):
        """
        插入一条数据
        :param sql:一条sql语句
        :return: 受影响的条数
        """
        rowcount = 0
        try:
            self.cursor.execute(sql)
            rowcount = self.cursor.rowcount
        except Exception as err:
            print('错误：',err,'，返回命令行参数：',rowcount)
            self.init_cursor()
        return rowcount

    def execute_many_for(self, sql_list: list):
        """
        批量插入数据,多条insert语句装载在列表中
        :param sql_list: 多条insert语句组成的列表
        :return: 每条sql插入成功受影响的条数组成的列表, 执行失败的sql列表
        """
        failed_sql = []
        effect_rows = []
        for sql in sql_list:
            try:
                self.cursor.execute(sql)
                # 受影响的条数计数
                effect_row = self.cursor.rowcount
                effect_rows.append(effect_row)
            except Exception as err:
                print(err, f'执行异常的sql为: {sql}')
                failed_sql.append(sql)
                self.init_cursor()
        return effect_rows, failed_sql

    def del_data(self, sql):
        """
        删除数据
        :param sql:删除sql语句
        :return: 受影响的条数
        """
        try:
            self.cursor.execute(sql)
        except Exception as err:
            print(err)
        return self.cursor.rowcount

    def execute_many(self, sql, params: list):
        """
        批量执行,用于批量执行insert语句
        :param sql: sql语句, eg: 'insert into stu values(?, ?, ?, ?)'
        :param params: 对应sql语句中的参数,按位置顺序传参;参数类型为[(),()]或[[],[]]
        :return: 受影响的记录条数
        """
        try:
            self.cursor.executemany(sql, params)
            return self.cursor.rowcount
        except Exception as err:
            print(f'!!!批量插入失败: {err}, 全部回滚<<<')

    def create_user_schema(self, user_schema, password):
        """
        创建用户和模式,并对用户授权
        :param user_schema: 用户名(模式名)
        :param password: 密码
        :return:
        """
        sql = [
            rf'CREATE USER "{user_schema}" IDENTIFIED BY "{password}" HASH WITH SHA512 NO SALT PASSWORD_POLICY 2 ENCRYPT BY "{password}" LIMIT FAILED_LOGIN_ATTEMPS 3, PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "MAIN";',
            rf'grant "PUBLIC","VTI","SOI" to "{user_schema}";',
            rf'grant CREATE SESSION to "{user_schema}";']
        sql2 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null'
        expected_results = (f'{user_schema}', f'{user_schema}')
        try:
            for i in sql:
                self.cursor.execute(i)
            select_results = self.select_data(sql2)
            if expected_results in select_results:
                return True
        except Exception as err:
            print(err)

    def del_user_schema(self, user_and_schema):
        """
        删除用户(模式)
        :param user_and_schema: 用户名(模式名)
        :return:
        """
        sql = f'drop user {user_and_schema} cascade;'
        sql2 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null'
        expected_results = (f'{user_and_schema}', f'{user_and_schema}')
        try:
            if expected_results in self.select_data(sql2):
                self.cursor.execute(sql)
                select_results = self.select_data(sql2)
                if expected_results not in select_results:
                    return True
            else:
                print(f'要删除的目标对象不存在: {user_and_schema}')
        except Exception as err:
            print(err)


if __name__ == '__main__':
    dm = DmDb()
    sql1 = 'select * from stu;'
    sql2 = """insert into stu values(1,'dmpython','男',20);"""
    sql3 = ["""insert into stu values(2,'dmpython','女',21);""",
            """insert into stu values(3,'dmpython','男',22);""",
            """insert into stu values(3,'dmpython','男',22);"""]
    sql4 = """delete from stu where "name" = 'dmpython'"""
    sql5 = 'delete from stu'
    # Seq_params = [(1, 'dmpython', '男', 20), (2, 'dmpython', '女', 21), (3, 'dmpython', '男', 22)]
    Seq_params = [[1, 'dmpython', '男', 20], [2, 'dmpython', '女', 21], [3, 'dmpython', '男', 22]]
    # Seq_params = [[1, 'dmpython', '男', 20], [2, 'dmpython', '女', 21], [2, 'dmpython', '男', 22]]
    sql_many = """insert into stu values(?, ?, ?, ?)"""
    table = 'stu'
    sql6 = """update stu set "name" = 'dm_manager_modified_dmPython' where "id" = 100;"""
    sql7 = ["""update stu set "name" = 'dm_manager_modified_dmPython' where "id" = 100;""",
            """update stu set "name" = 'dm_manager_modified_dmPython' where "id" = 99;"""]
    sql8 = ["""insert into stu values(1,'dmpython','男',20);""",
            """update stu set "name" = 'dm_manager_modified_dmPython' where "id" = 1;""",
            """update stu set "name" = 'dm_manager_modified_dmPython' where "id" = 100;"""]
    # dm_admin = DmDb(user='SYSDBA', password='123456789')
    # sql9 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;'
    # print(dm_admin.create_user_schema('LSZ_INSIDE', 'Gwxa123456'))
    dm.execute_one(sql2)
    dm.execute_one(sql2)
    dm.execute_one(sql2)
    dm.execute_one(sql2)
    dm.execute_many_for(sql3)
    pass
